package com.java1234.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.java1234.dao.IBookDao;
import com.java1234.model.Book;
import com.java1234.util.StringUtil;

public class BookImpl implements IBookDao {

	/**
	 * 数据库图书信息添加
	 */
	public int add(Connection con, Book book) throws Exception {
		String sql = "insert into t_book values(null,?,?,?,?,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setString(2, book.getAuthor());
		pstmt.setString(3, book.getSex());
		pstmt.setFloat(4, book.getPrice());
		pstmt.setInt(5, book.getBookTypeId());
		pstmt.setString(6, book.getBookTypeDesc());
		pstmt.setString(7, "在馆");
		int i = pstmt.executeUpdate();

		return i;
	}

	/**
	 * 数据库图书信息查询
	 * 
	 * @param con
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public ResultSet list(Connection con, Book book) throws Exception {
		StringBuffer sb = new StringBuffer("select * from t_book b,t_bookType bt where b.bookTypeId=bt.id");
		if (StringUtil.isNotEmpty(book.getBookName())) {
			sb.append(" and b.bookName like '%" + book.getBookName() + "%'");
		}
		if (StringUtil.isNotEmpty(book.getAuthor())) {
			sb.append(" and b.author like '%" + book.getAuthor() + "%'");
		}
		if (book.getBookTypeId() != null && book.getBookTypeId() != -1) {
			sb.append(" and b.bookTypeId=" + book.getBookTypeId());
		}
		PreparedStatement pstmt = con.prepareStatement(sb.toString());
		ResultSet i = pstmt.executeQuery();
		return i;
	}

	/**
	 * 数据库图书信息删除
	 * 
	 * @param con
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public int delete(Connection con, String id) throws Exception {
		String sql = "delete from t_book where id=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, id);
		int i = pstmt.executeUpdate();

		return i;
	}

	/**
	 * 数据库图书信息修改
	 * 
	 * @param con
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public int update(Connection con, Book book) throws Exception {
		String sql = "update t_book set bookName=?,author=?,sex=?,price=?,bookDesc=?,bookTypeId=? where id=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setString(2, book.getAuthor());
		pstmt.setString(3, book.getSex());
		pstmt.setFloat(4, book.getPrice());
		pstmt.setString(5, book.getBookTypeDesc());
		pstmt.setInt(6, book.getBookTypeId());
		pstmt.setInt(7, book.getId());
		int i = pstmt.executeUpdate();

		return i;
	}

	/**
	 * 数据库指定图书类别下是否存在图书
	 * 
	 * @param con
	 * @param bookTypId
	 * @return
	 * @throws Exception
	 */
	public boolean existBookByBookTypeId(Connection con, String bookTypeId) throws Exception {
		String sql = "select * from t_book where bookTypeId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, bookTypeId);
		ResultSet rs = pstmt.executeQuery();
		return rs.next();
	}
}
